﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using produccion.Conexion;
using Oracle.DataAccess.Client;
using Oracle.DataAccess.Types;
using System.Windows;
using System.Windows.Controls;
using System.IO;
using System.Xml;

namespace produccion.clases
{
    class CLSTEMPORAL:ObservableObject
    {

        MDOgeneral mod = new MDOgeneral();
       
        public DataTable TMPANACONS = new DataTable("TMPANACONS");
        public DataTable TMPANACONS1 = new DataTable("TMPANACONS1");
        public void SP_INFANACONS(string grupos, DateTime desde, DateTime hasta)
        {
            try
            {
                MDOgeneral.spComando.CommandText = "SP_INFANACONS";
                MDOgeneral.spComando.Connection = MDOgeneral.conexion;
                MDOgeneral.spComando.CommandType = CommandType.StoredProcedure;
                MDOgeneral.spComando.Parameters.Clear();
                
                //Input
                MDOgeneral.spComando.Parameters.Add("pva_grupos", Oracle.DataAccess.Client.OracleDbType.Varchar2, 40).Value = grupos;
                MDOgeneral.spComando.Parameters["pva_grupos"].Direction = ParameterDirection.Input;

                MDOgeneral.spComando.Parameters.Add("pdt_desde", Oracle.DataAccess.Client.OracleDbType.Date, 40).Value = desde;
                MDOgeneral.spComando.Parameters["pdt_desde"].Direction = ParameterDirection.Input;

                MDOgeneral.spComando.Parameters.Add("pdt_hasta", Oracle.DataAccess.Client.OracleDbType.Date, 40).Value = hasta;
                MDOgeneral.spComando.Parameters["pdt_hasta"].Direction = ParameterDirection.Input;

                //Output
                MDOgeneral.spComando.Parameters.Add("pva_result", Oracle.DataAccess.Client.OracleDbType.Varchar2, 2000).Direction = ParameterDirection.Output;
                MDOgeneral.spComando.Parameters.Add("pva_errusu", Oracle.DataAccess.Client.OracleDbType.Varchar2, 2000).Direction = ParameterDirection.Output;
                MDOgeneral.spComando.ExecuteNonQuery();
                string c = MDOgeneral.spComando.Parameters["pva_result"].Value.ToString();
                TMPANACONS = mod.consulta("select * from TMPANACONS");
            
            }
            catch (Exception ex) { throw ex; }
        }
        public void SPD_INFANACONS(string codgrp,string codref,string codcol)
        {
            try {
                TMPANACONS1 = mod.consulta("select * from TMPANACONS1 where (CODGRP_TMP='" + codgrp + "' and CODREF_TMP='" + codref + "' and CODCOL_TMP='"+codcol+"')");
            }
            catch (Exception ex) { throw ex; }
        }

        public DataTable TMPCUMPED = new DataTable("TMPCUMPED");
        public void SP_INFCUMPED(string cliente, DateTime desde, DateTime hasta) 
        {
            try 
            {
                MDOgeneral.spComando.CommandText = "SP_INFCUMPED";
                MDOgeneral.spComando.Connection = MDOgeneral.conexion;
                MDOgeneral.spComando.CommandType = CommandType.StoredProcedure;
                MDOgeneral.spComando.Parameters.Clear();

                //Input
                MDOgeneral.spComando.Parameters.Add("pva_cliini", Oracle.DataAccess.Client.OracleDbType.Varchar2, 40).Value = String.IsNullOrEmpty(cliente)?"   ":cliente;
                MDOgeneral.spComando.Parameters["pva_cliini"].Direction = ParameterDirection.Input;

                MDOgeneral.spComando.Parameters.Add("pva_clifin", Oracle.DataAccess.Client.OracleDbType.Varchar2, 40).Value = String.IsNullOrEmpty(cliente)?"zzz":cliente;
                MDOgeneral.spComando.Parameters["pva_clifin"].Direction = ParameterDirection.Input;

                MDOgeneral.spComando.Parameters.Add("pdt_desde", Oracle.DataAccess.Client.OracleDbType.Date, 40).Value = desde;
                MDOgeneral.spComando.Parameters["pdt_desde"].Direction = ParameterDirection.Input;

                MDOgeneral.spComando.Parameters.Add("pdt_hasta", Oracle.DataAccess.Client.OracleDbType.Date, 40).Value = hasta;
                MDOgeneral.spComando.Parameters["pdt_hasta"].Direction = ParameterDirection.Input;

                 //Output
                MDOgeneral.spComando.Parameters.Add("p_ResultSet", Oracle.DataAccess.Client.OracleDbType.RefCursor).Direction = ParameterDirection.Output;
                
                MDOgeneral.spComando.ExecuteNonQuery();
                //string c = MDOgeneral.spComando.Parameters["pva_result"].Value.ToString();

                TMPCUMPED = mod.consulta("select * from TMPCUMPED order by CODCLI_TMP,NROPED_TMP asc");
            }
            catch (Exception ex) { throw ex; }
        }

        public DataTable TMPENCPPCPXX = new DataTable("TMPENCPPCPXX");
        public void CONSENCPPCPXX(string cliente, string strPedido)
        {
            try
            {
                TMPENCPPCPXX.Clear();
                string query = "";
                query = "select * from (select nroped_epc, fecped_epc, estped_epc,fecent_epc,pedori_epc,sum(canped_dpc) as canped_epc, sum(canent_dpc) as canent_epc, sum(candes_dpc) as candes_epc,(sum(canent_dpc)-(sum(canped_dpc)-sum(candes_dpc))) as canmas_epc, (sum(canped_dpc)-(sum(canent_dpc)+sum(candes_dpc))) as canpen_dpc from ENCPPCP,DETPPCP where (CODCLI_EPC=CODCLI_DPC and NROPED_EPC=NROPED_DPC) and (codcli_epc='" + cliente + "') and (nroped_epc>='" + strPedido + "' ) group by nroped_epc, fecped_epc, estped_epc,fecent_epc,pedori_epc ORDER BY fecped_epc desc) where canpen_dpc>0";
                TMPENCPPCPXX = mod.consulta(query);
            }
            catch (Exception ex) { throw ex; }
        }

        public DataTable dtPedidosCliente = new DataTable("dtPedidosCliente");
        public DataTable dtDetallePedCli = new DataTable("dtDetallePedCli");
        public DataTable dtDetalleItems = new DataTable("dtDetalleItems");

        public DataTable dtPedidosPorCliente = new DataTable("dtPedidosPorCliente");//FJRF - Para saber los pedidos de un cliente
        public void CONSENCPPCP(string cliente, string subcliente, DateTime desde, DateTime hasta, string estado, String strLineasSeleccionadas)
        {
            try
            {
                if (strLineasSeleccionadas == null)//FJRF - Si viene nula hacemos lo siguiente
                {
                    strLineasSeleccionadas = "";//FJRF - Enviamos la variable vacia
                }
                dtPedidosCliente.Clear();

                MDOgeneral.spComando.CommandText = "SP_INFRESPEDCLI";
                MDOgeneral.spComando.Connection = MDOgeneral.conexion;
                MDOgeneral.spComando.CommandType = CommandType.StoredProcedure;
                MDOgeneral.spComando.Parameters.Clear();

                //Input
                MDOgeneral.spComando.Parameters.Add("pva_cliini", Oracle.DataAccess.Client.OracleDbType.Varchar2, 40).Value = String.IsNullOrEmpty(cliente) ? "   " : cliente;
                MDOgeneral.spComando.Parameters["pva_cliini"].Direction = ParameterDirection.Input;
                MDOgeneral.spComando.Parameters.Add("pva_clifin", Oracle.DataAccess.Client.OracleDbType.Varchar2, 40).Value = String.IsNullOrEmpty(cliente) ? "zzz" : cliente;
                MDOgeneral.spComando.Parameters["pva_clifin"].Direction = ParameterDirection.Input;

                MDOgeneral.spComando.Parameters.Add("pva_subini", Oracle.DataAccess.Client.OracleDbType.Varchar2, 40).Value = String.IsNullOrEmpty(subcliente) ? "   " : subcliente;
                MDOgeneral.spComando.Parameters["pva_subini"].Direction = ParameterDirection.Input;
                MDOgeneral.spComando.Parameters.Add("pva_subfin", Oracle.DataAccess.Client.OracleDbType.Varchar2, 40).Value = String.IsNullOrEmpty(subcliente) ? "zzz" : subcliente;
                MDOgeneral.spComando.Parameters["pva_subfin"].Direction = ParameterDirection.Input;

                MDOgeneral.spComando.Parameters.Add("pdt_desde", Oracle.DataAccess.Client.OracleDbType.Date, 40).Value = desde;
                MDOgeneral.spComando.Parameters["pdt_desde"].Direction = ParameterDirection.Input;
                MDOgeneral.spComando.Parameters.Add("pdt_hasta", Oracle.DataAccess.Client.OracleDbType.Date, 40).Value = hasta;
                MDOgeneral.spComando.Parameters["pdt_hasta"].Direction = ParameterDirection.Input;

                MDOgeneral.spComando.Parameters.Add("pva_estado", Oracle.DataAccess.Client.OracleDbType.Varchar2, 40).Value = estado;
                MDOgeneral.spComando.Parameters["pva_estado"].Direction = ParameterDirection.Input;

                MDOgeneral.spComando.Parameters.Add("pva_lineas", Oracle.DataAccess.Client.OracleDbType.Varchar2, 1000).Value = strLineasSeleccionadas;
                MDOgeneral.spComando.Parameters["pva_lineas"].Direction = ParameterDirection.Input;

                MDOgeneral.spComando.Parameters.Add("pva_solcom", Oracle.DataAccess.Client.OracleDbType.Varchar2, 40).Value = MDOgeneral.StrSoloClientesComerciales;
                MDOgeneral.spComando.Parameters["pva_solcom"].Direction = ParameterDirection.Input;


                //Output
                MDOgeneral.spComando.Parameters.Add("pva_result", Oracle.DataAccess.Client.OracleDbType.Varchar2, 2000).Direction = ParameterDirection.Output;
                MDOgeneral.spComando.Parameters.Add("pva_errusu", Oracle.DataAccess.Client.OracleDbType.Varchar2, 2000).Direction = ParameterDirection.Output;
                MDOgeneral.spComando.ExecuteNonQuery();
                string c = MDOgeneral.spComando.Parameters["pva_result"].Value.ToString();
                
                dtPedidosCliente = mod.consulta("select CODCLI_TMP, NOMCLI_TMP, CODSUB_TMP, NOMSUB_TMP, NROPED_TMP, FECPED_TMP, ESTPED_TMP, FECENT_TMP, NROORI_TMP, CANPED_TMP, CANENT_TMP, CANDES_TMP,CANMAS_TMP, CANPEN_TMP, DIASVENC_TMP, COLOR_TMP, ASTER_TMP from tmpinfrespedcli ");
                dtDetallePedCli = mod.consulta("select CODCLI_TMP, NROPED_TMP, CODREF_TMP, CODCOL_TMP, CANPED_TMP, CANENT_TMP, CANDES_TMP, CANMAS_TMP, CANPEN_TMP, NOMREF_TMP, NOMCOL_TMP, PREPRO_TMP, TOTAL_TMP from tmpinfrespedcli1 ");
                dtDetalleItems = mod.consulta("select CODCLI_TMP, NROPED_TMP, CODREF_TMP, CODCOL_TMP, CONSEC_TMP, LINEA_TMP, T1_TMP, T2_TMP, T3_TMP, T4_TMP, T5_TMP, T6_TMP, T7_TMP, T8_TMP, T9_TMP, T10_TMP, T11_TMP, T12_TMP, T13_TMP, T14_TMP, T15_TMP, T16_TMP, T17_TMP, T18_TMP, T19_TMP, T20_TMP, TTT_TMP from tmpinfrespedcli2 ");

                dtPedidosPorCliente = mod.consulta("select CODCLI_TMP AS CODIGO, NOMCLI_TMP AS CLIENTE, SUM(CANPED_TMP) AS PEDIDOS from tmpinfrespedcli GROUP BY CODCLI_TMP, NOMCLI_TMP ORDER BY CODCLI_TMP ");
            }
            catch (Exception ex) { throw ex; }
        }
       
        public DataTable TMPDETPPCP = new DataTable("TMPDETPPCP");
        public void CONSDETPPCP(string cliente, string pedido) 
        {
            string strPedido = "";
            try 
            {
                if (pedido.Length > 5)
                    strPedido = pedido.Substring(3).Trim();
                else
                    strPedido = pedido.Trim();

                string query = "select codref_dpc,(select NOMREF_REF from maeref where CODREF_REF=codref_dpc) as NOMREF_REF, codcol_dpc, (select nomcol_col from maecol where codcol_col=CODCOL_DPC) as nomcol_col, CANPED_DPC,CANENT_DPC,CANDES_DPC,(canent_dpc-(canped_dpc-candes_dpc)) as canmas_dpc, (canped_dpc-(canent_dpc + candes_dpc)) as canpen_dpc from detppcp where (CODCLI_DPC='" + cliente + "' and NROPED_DPC='" + strPedido + "')";
                TMPDETPPCP = mod.consulta(query);
            }
            catch (Exception ex) { throw ex; }
        }

        public DataTable TMPPEDCLITAL = new DataTable("TMPPEDCLITAL");
        public void SP_INFPEDCLITAL(string codcliini, string codclifin, DateTime desde, DateTime hasta, string codref, string colores, string estado, string strCodigoColeccion) 
        {
            try
            {

                MDOgeneral.spComando.CommandText = "SP_INFPEDCLITAL";
                MDOgeneral.spComando.Connection = MDOgeneral.conexion;
                MDOgeneral.spComando.CommandType = CommandType.StoredProcedure;
                MDOgeneral.spComando.Parameters.Clear();
                
                //Input
                MDOgeneral.spComando.Parameters.Add("pva_cliini", Oracle.DataAccess.Client.OracleDbType.Varchar2, 40).Value = String.IsNullOrEmpty(codcliini) ? "   " : codcliini;
                MDOgeneral.spComando.Parameters["pva_cliini"].Direction = ParameterDirection.Input;

                MDOgeneral.spComando.Parameters.Add("pva_clifin", Oracle.DataAccess.Client.OracleDbType.Varchar2, 40).Value = String.IsNullOrEmpty(codclifin) ? "zzz" : codclifin;
                MDOgeneral.spComando.Parameters["pva_clifin"].Direction = ParameterDirection.Input;

                MDOgeneral.spComando.Parameters.Add("pdt_desde", Oracle.DataAccess.Client.OracleDbType.Date, 40).Value = desde;
                MDOgeneral.spComando.Parameters["pdt_desde"].Direction = ParameterDirection.Input;

                MDOgeneral.spComando.Parameters.Add("pdt_hasta", Oracle.DataAccess.Client.OracleDbType.Date, 40).Value = hasta;
                MDOgeneral.spComando.Parameters["pdt_hasta"].Direction = ParameterDirection.Input;

                MDOgeneral.spComando.Parameters.Add("pva_refini", Oracle.DataAccess.Client.OracleDbType.Varchar2, 40).Value = String.IsNullOrEmpty(codref) ? "    " : codref;
                MDOgeneral.spComando.Parameters["pva_refini"].Direction = ParameterDirection.Input;
                
                MDOgeneral.spComando.Parameters.Add("pva_reffin", Oracle.DataAccess.Client.OracleDbType.Varchar2, 40).Value = String.IsNullOrEmpty(codref) ? "zzzz" : codref;
                MDOgeneral.spComando.Parameters["pva_reffin"].Direction = ParameterDirection.Input;
                
                MDOgeneral.spComando.Parameters.Add("pva_colores", Oracle.DataAccess.Client.OracleDbType.Varchar2, 1000).Value = colores;
                MDOgeneral.spComando.Parameters["pva_colores"].Direction = ParameterDirection.Input;
              
                MDOgeneral.spComando.Parameters.Add("pva_estado", Oracle.DataAccess.Client.OracleDbType.Varchar2, 40).Value = estado;
                MDOgeneral.spComando.Parameters["pva_estado"].Direction = ParameterDirection.Input;

                MDOgeneral.spComando.Parameters.Add("lva_clc", Oracle.DataAccess.Client.OracleDbType.Varchar2, 40).Value = strCodigoColeccion;
                MDOgeneral.spComando.Parameters["lva_clc"].Direction = ParameterDirection.Input;

                //Output
                MDOgeneral.spComando.Parameters.Add("pva_result", Oracle.DataAccess.Client.OracleDbType.Varchar2, 2000).Direction = ParameterDirection.Output;
                MDOgeneral.spComando.Parameters.Add("pva_errusu", Oracle.DataAccess.Client.OracleDbType.Varchar2, 2000).Direction = ParameterDirection.Output;
                MDOgeneral.spComando.ExecuteNonQuery();
                string c = MDOgeneral.spComando.Parameters["pva_result"].Value.ToString();

                TMPPEDCLITAL = mod.consulta("select (CODREF_TMP||CODCOL_TMP) as REFE,CODLIN_TMP,NOMLIN_TMP,CODREF_TMP,NOMREF_TMP,CODCOL_TMP,NOMCOL_TMP,T1_TMP,T2_TMP,T3_TMP,  T4_TMP,T5_TMP,T6_TMP,T7_TMP,T8_TMP,T9_TMP,T10_TMP,T11_TMP,T12_TMP,T13_TMP,T14_TMP,T15_TMP,T16_TMP,T17_TMP,T18_TMP,T19_TMP,T20_TMP,TOTAL_TMP from  TMPPEDCLITAL");
 
            } catch (Exception ex) { throw ex; }
        }
        
        public DataTable TMPPEDCLIREF = new DataTable("TMPPEDCLIREF");
        public void SP_INFPEDCLIREF(string codref, string colores, string codcli,string estado,DateTime desde,DateTime hasta) 
        {
            try 
            {
                MDOgeneral.spComando.CommandText = "SP_INFPEDCLIREF";
                MDOgeneral.spComando.Connection = MDOgeneral.conexion;
                MDOgeneral.spComando.CommandType = CommandType.StoredProcedure;
                MDOgeneral.spComando.Parameters.Clear();

                //Input
                MDOgeneral.spComando.Parameters.Add("pva_codref", Oracle.DataAccess.Client.OracleDbType.Varchar2, 40).Value = codref;
                MDOgeneral.spComando.Parameters["pva_codref"].Direction = ParameterDirection.Input;

                MDOgeneral.spComando.Parameters.Add("pva_colores", Oracle.DataAccess.Client.OracleDbType.Varchar2, 1000).Value = colores;
                MDOgeneral.spComando.Parameters["pva_colores"].Direction = ParameterDirection.Input;

                MDOgeneral.spComando.Parameters.Add("pva_cliini", Oracle.DataAccess.Client.OracleDbType.Varchar2, 40).Value = String.IsNullOrEmpty(codcli) ? "   " : codcli;
                MDOgeneral.spComando.Parameters["pva_cliini"].Direction = ParameterDirection.Input;

                MDOgeneral.spComando.Parameters.Add("pva_clifin", Oracle.DataAccess.Client.OracleDbType.Varchar2, 40).Value = String.IsNullOrEmpty(codcli) ? "zzz" : codcli;
                MDOgeneral.spComando.Parameters["pva_clifin"].Direction = ParameterDirection.Input;

                MDOgeneral.spComando.Parameters.Add("pva_estado", Oracle.DataAccess.Client.OracleDbType.Varchar2, 40).Value = estado;
                MDOgeneral.spComando.Parameters["pva_estado"].Direction = ParameterDirection.Input;

                MDOgeneral.spComando.Parameters.Add("pdt_desde", Oracle.DataAccess.Client.OracleDbType.Date, 40).Value = desde;
                MDOgeneral.spComando.Parameters["pdt_desde"].Direction = ParameterDirection.Input;

                MDOgeneral.spComando.Parameters.Add("pdt_hasta", Oracle.DataAccess.Client.OracleDbType.Date, 40).Value = hasta;
                MDOgeneral.spComando.Parameters["pdt_hasta"].Direction = ParameterDirection.Input;
                                
                //Output
                MDOgeneral.spComando.Parameters.Add("pva_result", Oracle.DataAccess.Client.OracleDbType.Varchar2, 2000).Direction = ParameterDirection.Output;
                MDOgeneral.spComando.Parameters.Add("pva_errusu", Oracle.DataAccess.Client.OracleDbType.Varchar2, 2000).Direction = ParameterDirection.Output;
                MDOgeneral.spComando.ExecuteNonQuery();
                string c = MDOgeneral.spComando.Parameters["pva_result"].Value.ToString();
                TMPPEDCLIREF = mod.consulta("select * from  TMPPEDCLIREF");      
            }
            catch (Exception ex){throw ex; }        
        }


        public DataTable prueba()
        {
            try
            {
                StringWriter swStringWriter = new StringWriter();
                List<pp> lista = new List<pp>() { new pp { C1 = "0039", C2 = "B" }, new pp { C1 = "C", C2 = "D" } };
                DataTable dt = toolsUtiles.ToDataTable(lista);
                dt.TableName = "t_Prueba";
                dt.WriteXml(swStringWriter);
                string xml = swStringWriter.ToString();
   
                string query = "SELECT * FROM lisprerepp PIVOT xml (MAX(CODLIS_LPR) FOR NOMLIS_LPR IN (select nomlis_lpr from lisprerepp))";
                DataTable val = mod.consulta(query);
                MessageBox.Show(val.Rows[0][0].ToString());

                XmlDocument xDoc = new XmlDocument();
                xDoc.LoadXml(val.Rows[0][0].ToString());
                XmlNodeList PivotSet = xDoc.GetElementsByTagName("PivotSet");

                foreach (XmlElement nodo in PivotSet)
                {
                    int i = 0;

                    XmlNodeList nColumna = nodo.GetElementsByTagName("column");
                    MessageBox.Show(nColumna[i].InnerText);
                    MessageBox.Show(nColumna[1].InnerText);
                } 

                DataTable valor = new DataTable();
                Stream s = new MemoryStream(ASCIIEncoding.Default.GetBytes(val.Rows[0][0].ToString()));
                valor.ReadXml(s);

                return valor;
        
            } catch(Exception ex) {throw ex;}
   
        }


        public DataTable consultaTallas(string strRef)
        {
            try
            {
                DataTable dtTallas = new DataTable();

                string query = "select a.codtje_tal, a.codtal_tal, a.postal_tal from taltal a inner join maeref b on a.codtje_tal=b.talref_ref where b.codref_ref='" + strRef + "' order by a.codtje_tal, a.postal_tal ";
                dtTallas = mod.consulta(query);

                return dtTallas;

            }
            catch (Exception ex) { throw ex; }

        }


        public class pp 
        {
            string _C1, _C2;
            public string C1{
                get { return _C1; }
                set { _C1 = value; }
            }

            public string C2
            {
                get { return _C2; }
                set { _C2 = value; }
            }
        
        }

        /*
        Envio y recivo de tablas OracleConexion for procedure
        public DataTable sp_prueba() {
            try {

                StringWriter swStringWriter = new StringWriter();
                List<pp> lista = new List<pp>() { new pp { C1 = "0039", C2 = "B" }, new pp { C1 = "C", C2 = "D" } };
                DataTable dt = toolsUtiles.ToDataTable(lista);
                dt.TableName = "t_Prueba";
                dt.WriteXml(swStringWriter);
                string xml = swStringWriter.ToString();
                               
                OracleConexion oc = new OracleConexion(Properties.Settings.Default.CadenaConexion);
                var r1 = new OracleParameter("t_Prueba", OracleDbType.XmlType, xml, ParameterDirection.Input);
                var r2 = new OracleParameter("p_ResultSet", OracleDbType.RefCursor, ParameterDirection.Output);
                var pararametros = new List<OracleParameter> { r1, r2 };
                DataSet ds = oc.EjecutarSp<DataSet>("sp_Prueba",pararametros);
                return ds.Tables[0];

            }
            catch (Exception ex) { throw ex; }
        }
        */

    
     




    }
}
